-- Query catch gathered from multiple DBs and housed in GF_MERGED_CATCH
-- Query species catch from GFFOS on SVBCPBSGFIIS
-- Last modified: 2015-04-09 (RH)
SET NOCOUNT ON 

-- Mean species weight calculated using `gfb_mean_weight.sql', which emulates PJS algorithm for GFBIO data
DECLARE @MEAN_WEIGHT TABLE (SPECIES_CODE VARCHAR(5), MNWT REAL)
INSERT INTO @MEAN_WEIGHT VALUES
  ('222', 1.759289),   -- ttype=c(1,4), gear=1
  ('396', 0.8536203),  -- ttype=c(1,4), gear=1
  ('401', 1.724491),   -- ttype=c(1,4), gear=1, major=3:9
  ('405', 1.916324),   -- ttype=c(1,4), gear=1
  ('418', 1.45),       -- Paul Starr conversion for 2014 YTR assessment
  ('442', 3.575088),   -- ttype=c(1,4), gear=5, major=3:9 (queried 150409)
  ('602', 1.124977),   -- ttype=c(1,4), gear=1, major=3:9
  ('621', 0.5346079)   -- ttype=c(1,4), gear=1
  --('222', 1.596781),  -- ttype=c(1,2,3,4), gear=1
  --('401', 1.453529),  -- ttype=c(1,2,3,4), gear=1
  --('405', 1.787811),  -- ttype=c(1,2,3,4), gear=1
  --('418', 1.484357),  -- ttype=c(1,2,3,4), gear=1
  --('621', 0.458456)   -- ttype=c(1,2,3,4), gear=1

-- Usage: SELECT FW.MEAN_WEIGHT FROM @MEAN_WEIGHT FW WHERE FW.SPECIES_CODE IN('222')

-- Gather catch of RRF, POP, ORF, TAR
SELECT --TOP 200
  MC.DATABASE_NAME,
  MC.TRIP_ID,
  MC.FISHING_EVENT_ID,
  --ISNULL(L.LOCALITY_DESCRIPTION,'MOOSE COUNTY') AS LOCAL_NAME,
  Sum(CASE
    WHEN MC.SPECIES_CODE IN (@sppcode) THEN COALESCE(NULLIF(MC.LANDED_KG,0), NULLIF(MC.LANDED_PCS*FW.MNWT,0), 0)
    ELSE 0 END) AS landed,
  Sum(CASE
    WHEN MC.SPECIES_CODE IN (@sppcode) THEN COALESCE(NULLIF(MC.DISCARDED_KG,0), NULLIF(MC.DISCARDED_PCS*FW.MNWT,0), 0)
    ELSE 0 END) AS discard,
  SUM(CASE
    WHEN MC.SPECIES_CODE IN ('396') THEN COALESCE(NULLIF(MC.LANDED_KG,0), NULLIF(MC.LANDED_PCS*FW.MNWT,0), 0)
    ELSE 0 END) AS POP,
  SUM(CASE  -- all rockfish other than POP
    WHEN MC.SPECIES_CODE IN (@orfcode) THEN COALESCE(NULLIF(MC.LANDED_KG,0), NULLIF(MC.LANDED_PCS*FW.MNWT,0), 0)
    ELSE 0 END) AS ORF,
  SUM(CASE  -- target landings reference for discard calculations
    WHEN MC.FISHERY_SECTOR IN ('GROUNDFISH TRAWL','JOINT VENTURE TRAWL') AND MC.SPECIES_CODE IN (@trfcode)
      THEN COALESCE(NULLIF(MC.LANDED_KG,0), NULLIF(MC.LANDED_PCS*FW.MNWT,0), 0)
    WHEN MC.FISHERY_SECTOR IN ('HALIBUT','HALIBUT AND SABLEFISH','K/L') AND MC.SPECIES_CODE IN ('614')
      THEN COALESCE(NULLIF(MC.LANDED_KG,0), NULLIF(MC.LANDED_PCS*FW.MNWT,0), 0)
    WHEN MC.FISHERY_SECTOR IN ('SABLEFISH') AND MC.SPECIES_CODE IN ('454','455')
      THEN COALESCE(NULLIF(MC.LANDED_KG,0), NULLIF(MC.LANDED_PCS*FW.MNWT,0), 0)
    WHEN MC.FISHERY_SECTOR IN ('SPINY DOGFISH') AND MC.SPECIES_CODE IN ('042','044')
      THEN COALESCE(NULLIF(MC.LANDED_KG,0), NULLIF(MC.LANDED_PCS*FW.MNWT,0), 0)
    WHEN MC.FISHERY_SECTOR IN ('LINGCOD') AND MC.SPECIES_CODE IN ('467')
      THEN COALESCE(NULLIF(MC.LANDED_KG,0), NULLIF(MC.LANDED_PCS*FW.MNWT,0), 0)
    WHEN MC.FISHERY_SECTOR IN ('SCHEDULE II') AND MC.SPECIES_CODE IN ('042','044','467')
      THEN COALESCE(NULLIF(MC.LANDED_KG,0), NULLIF(MC.LANDED_PCS*FW.MNWT,0), 0)
    WHEN MC.FISHERY_SECTOR IN ('ROCKFISH INSIDE','ROCKFISH OUTSIDE','ZN','K/ZN') AND MC.SPECIES_CODE IN ('424','407','431','433','442')
      THEN COALESCE(NULLIF(MC.LANDED_KG,0), NULLIF(MC.LANDED_PCS*FW.MNWT,0), 0)
    ELSE 0 END) AS TAR
INTO #CATCH_CORE
FROM 
  GF_MERGED_CATCH MC LEFT OUTER JOIN
  @MEAN_WEIGHT FW ON
    MC.SPECIES_CODE = FW.SPECIES_CODE
GROUP BY MC.DATABASE_NAME, MC.TRIP_ID, MC.FISHING_EVENT_ID

-- Collect event information
SELECT --top 100
  MC.DATABASE_NAME,
  MC.TRIP_ID,
  MC.FISHING_EVENT_ID,
  (CASE
    WHEN MC.FISHERY_SECTOR IN ('GROUNDFISH TRAWL','JOINT VENTURE TRAWL') THEN 1
    WHEN MC.FISHERY_SECTOR IN ('HALIBUT','HALIBUT AND SABLEFISH','K/L') THEN 2
    WHEN MC.FISHERY_SECTOR IN ('SABLEFISH') THEN 3
    WHEN MC.FISHERY_SECTOR IN ('LINGCOD','SPINY DOGFISH','SCHEDULE II') THEN 4
    WHEN MC.FISHERY_SECTOR IN ('ROCKFISH INSIDE','ROCKFISH OUTSIDE','ZN','K/ZN') THEN 5
    WHEN MC.FISHERY_SECTOR IN ('FOREIGN') THEN 9
    ELSE 0 END) AS \"fid\",
  MC.FISHERY_SECTOR AS \"sector\",
  CASE
    WHEN MC.GEAR IN ('TRAWL') AND MC.GEAR NOT IN ('MIDWATER TRAWL') THEN 1
    WHEN MC.GEAR IN ('TRAP') THEN 2
    WHEN MC.GEAR IN ('TRAWL') AND MC.GEAR IN ('MIDWATER TRAWL') THEN 3
    WHEN MC.GEAR IN ('HOOK AND LINE') THEN 4
    WHEN MC.GEAR IN ('LONGLINE') THEN 5
    WHEN MC.GEAR IN ('LONGLINE OR HOOK AND LINE','TRAP OR LONGLINE OR HOOK AND LINE') THEN 8
    ELSE 0 END AS \"gear\",
  CASE
    WHEN MC.LOG_TYPE IN ('OBSERVER LOG') THEN 1
    WHEN MC.LOG_TYPE IN ('FISHER LOG') THEN 2
    WHEN MC.LOG_TYPE IN ('DMP') THEN 3
    WHEN MC.LOG_TYPE IN ('UNKNOWN') THEN 0
    ELSE 0 END AS \"log\",
  --CONVERT(VARCHAR(10),COALESCE(MC.FE_START_DATE, MC.FE_END_DATE, MC.TRIP_START_DATE, MC.TRIP_END_DATE),20) as \"date\",
  CONVERT(VARCHAR(10),MC.BEST_DATE,20) as \"date\",
  ISNULL(MC.MAJOR_STAT_AREA_CODE,'0') AS \"major\",
  ISNULL(MC.MINOR_STAT_AREA_CODE,'0') AS \"minor\",
  ISNULL(MC.LOCALITY_CODE,'0') AS \"locality\",
  --CC.LOCAL_NAME, -- for debugging locality names
  ISNULL(MC.BEST_DEPTH,0) AS \"fdep\"
INTO #EVENT_CORE
FROM 
  GF_MERGED_CATCH MC
--WHERE
  -- Langara Spit (Norm's choice based on the word "Langara" appearing in locality name)
  --(( MC.MAJOR_STAT_AREA_CODE IN (8) AND MC.MINOR_STAT_AREA_CODE IN (3) AND MC.LOCALITY_CODE IN (3) ) OR
  --( MC.MAJOR_STAT_AREA_CODE IN (9) AND MC.MINOR_STAT_AREA_CODE IN (35) AND MC.LOCALITY_CODE IN (1,2,4,5,6,7) ))
GROUP BY
  MC.DATABASE_NAME,
  MC.TRIP_ID,
  MC.FISHING_EVENT_ID,
  (CASE
    WHEN MC.FISHERY_SECTOR IN ('GROUNDFISH TRAWL','JOINT VENTURE TRAWL') THEN 1
    WHEN MC.FISHERY_SECTOR IN ('HALIBUT','HALIBUT AND SABLEFISH','K/L') THEN 2
    WHEN MC.FISHERY_SECTOR IN ('SABLEFISH') THEN 3
    WHEN MC.FISHERY_SECTOR IN ('LINGCOD','SPINY DOGFISH','SCHEDULE II') THEN 4
    WHEN MC.FISHERY_SECTOR IN ('ROCKFISH INSIDE','ROCKFISH OUTSIDE','ZN','K/ZN') THEN 5
    WHEN MC.FISHERY_SECTOR IN ('FOREIGN') THEN 9
    ELSE 0 END),
  MC.FISHERY_SECTOR,
  CASE
    WHEN MC.GEAR IN ('TRAWL') AND MC.GEAR NOT IN ('MIDWATER TRAWL') THEN 1
    WHEN MC.GEAR IN ('TRAP') THEN 2
    WHEN MC.GEAR IN ('TRAWL') AND MC.GEAR IN ('MIDWATER TRAWL') THEN 3
    WHEN MC.GEAR IN ('HOOK AND LINE') THEN 4
    WHEN MC.GEAR IN ('LONGLINE') THEN 5
    WHEN MC.GEAR IN ('LONGLINE OR HOOK AND LINE','TRAP OR LONGLINE OR HOOK AND LINE') THEN 8
    ELSE 0 END,
  CASE
    WHEN MC.LOG_TYPE IN ('OBSERVER LOG') THEN 1
    WHEN MC.LOG_TYPE IN ('FISHER LOG') THEN 2
    WHEN MC.LOG_TYPE IN ('DMP') THEN 3
    WHEN MC.LOG_TYPE IN ('UNKNOWN') THEN 0
    ELSE 0 END,
  CONVERT(VARCHAR(10),MC.BEST_DATE,20),
  ISNULL(MC.MAJOR_STAT_AREA_CODE,'0'),
  ISNULL(MC.MINOR_STAT_AREA_CODE,'0'),
  ISNULL(MC.LOCALITY_CODE,'0'),
  --CC.LOCAL_NAME, -- for debugging locality names
  ISNULL(MC.BEST_DEPTH,0)

-- Combine event information with catch
SELECT
  EC.DATABASE_NAME AS db,
  EC.TRIP_ID AS trip,
  EC.FISHING_EVENT_ID AS event,
  EC.fid,
  EC.sector,
  EC.gear,
  EC.log,
  EC.date,
  EC.major,
  EC.minor,
  EC.locality,
  --CC.LOCAL_NAME, -- for debugging locality names
  EC.fdep,
  CC.landed,
  CC.discard,
  CC.POP, CC.ORF, CC.TAR
--INTO #CATCH_EVENTS
FROM 
  #EVENT_CORE EC INNER JOIN
  #CATCH_CORE CC ON
    EC.DATABASE_NAME = CC.DATABASE_NAME AND
    EC.TRIP_ID = CC.TRIP_ID AND
    EC.FISHING_EVENT_ID = CC.FISHING_EVENT_ID 
WHERE
  CC.landed>0 OR CC.discard>0 OR CC.POP>0 OR CC.ORF>0 OR CC.TAR>0
--WHERE 
--  MC.SPECIES_CODE IN (@sppcode) --AND 
  --(MC.FISHERY_SECTOR IN ('GROUNDFISH TRAWL') OR 
  --(MC.FISHERY_SECTOR NOT IN ('GROUNDFISH TRAWL') AND ISNULL(MC.DATA_SOURCE_CODE,0) NOT IN (106,107)))
ORDER BY EC.TRIP_ID, EC.FISHING_EVENT_ID

--SELECT * 
--FROM #CATCH_EVENTS ORF
--WHERE
--  ORF.\"landed\">0 OR ORF.\"discard\">0 OR ORF.POP>0 OR ORF.ORF>0 OR ORF.TAR>0

-- getData("fos_mcatORF.sql","GFFOS",strSpp="442")
-- qu("fos_mcatORF.sql",dbName="GFFOS",strSpp="442")



